package cn.gemframe.utils;

import java.io.File;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;

public class GemExcelUtils {
	
	private int SPLIT_COUNT = 200; //Excel每个工作簿的行数

	private ArrayList<String> fieldName = null; //excel标题数据集

	private ArrayList<ArrayList<String>> fieldData = null; //excel数据内容	

	private HSSFWorkbook workBook = null;
	
	public GemExcelUtils(ArrayList<String> fieldName, ArrayList<ArrayList<String>> fieldData) {
		this.fieldName = fieldName;
		this.fieldData = fieldData;
	}
	
	public GemExcelUtils(){}

	/**
	 * 方法描述： 读取excel文件    
	 * 创建人：zxy
	 * 创建时间：2017年8月22日 下午5:20:25
	 * @param file
	 * @version
	 */
	public static List<List<String>> readExcelToObj(File file) throws Exception {
		Workbook wb = WorkbookFactory.create(file);
		return readExcel(wb, 0, 0, 0);
	}
	
	/**
	 * 方法描述： 读取File类型excel文件    
	 * 创建人：zxy
	 * 创建时间：2017年8月22日 下午5:20:25
	 * @param file
	 * @version
	 */
	public static List<List<String>> readExcelToObject(File file,Integer startIndex) throws Exception {
		Workbook wb = WorkbookFactory.create(file);
		return readExcel(wb, 0, startIndex, 0);
	}
	
	/**
	 * 方法描述： 读取excel文件    
	 * 创建人：zxy
	 * 创建时间：2017年8月22日 下午5:20:25
	 * @param inputStream
	 * @version
	 */
	public static List<List<String>> readExcelToObj(InputStream inputStream) throws Exception {
		Workbook wb = WorkbookFactory.create(inputStream);
		return readExcel(wb, 0, 0, 0);
	}
	
	/**
	 * 方法描述： 读取excel文件    
	 * 创建人：zxy
	 * 创建时间：2017年8月22日 下午5:20:25
	 * @param inputStream
	 * @version
	 */
	public static List<List<String>> readExcelToObj(InputStream inputStream,int startReadLine) throws Exception {
		Workbook wb = WorkbookFactory.create(inputStream);
		return readExcel(wb, 0, startReadLine, 0);
	}

	/**
	 * 方法描述： 读取excel文件    
	 * 创建人：zxy
	 * 创建时间：2017年8月22日 下午5:20:25
	 * @param path
	 * @version
	 */
	public static List<List<String>> readExcelToObj(String path) throws Exception {
		Workbook wb = WorkbookFactory.create(new File(path));
		return readExcel(wb, 0, 0, 0);
	}
	
	/**
	 * 方法描述：创建excel表格 
	 * 创建人：zxy
	 * 创建时间：2017年8月29日 下午1:26:05
	 * @param response
	 * @param exelName：表格的名字
	 * @param pageFlag：是否分页
	 * @param pageNumber：每页数据量
	 * @version
	 */
	public void expordExcel(HttpServletResponse response,String exelName,Boolean pageFlag,int pageNumber) throws Exception {
		OutputStream os = response.getOutputStream();
		String filename =exelName+".xls";
		filename = new String(filename.getBytes("gbk"),"iso-8859-1");
		response.reset();
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-disposition", "attachement;filename="+filename);
		response.setBufferSize(1024);
		if(pageFlag){
			SPLIT_COUNT=pageNumber;
			workBook = createWorkbook();
		}else{
			workBook = createWorkbookNoPage();
		}
		
		workBook.write(os);//将excel中的数据写到输出流中，用于文件的输出
		os.close();
	}
	
	
	public HSSFWorkbook createWorkbookNoPage() {
		workBook = new HSSFWorkbook();
		HSSFSheet sheet = workBook.createSheet("Sheet1");
		HSSFRow headRow = sheet.createRow((short) 0);
		//创建标题列
		createExcelTitel(headRow);
		createExcelContextNoPage(sheet);
		return workBook;
	}
	
	//创建excel
	public HSSFWorkbook createWorkbook() {
		workBook = new HSSFWorkbook();
		int rows = fieldData.size();
		int sheetNum = 0;
		if (rows % SPLIT_COUNT == 0) {
			sheetNum = rows / SPLIT_COUNT;
		} else {
			sheetNum = rows / SPLIT_COUNT + 1;
		}
		for (int i = 1; i <= sheetNum; i++) {
			HSSFSheet sheet = workBook.createSheet("Sheet" + i);
			HSSFRow headRow = sheet.createRow((short) 0);
			//创建标题列
			createExcelTitel(headRow);
			//创建内容
			createExcelContext(sheet,i,rows);
		}
		return workBook;
	}
	//创建表格里面的标题
	public void createExcelTitel(HSSFRow headRow){
		for (int j = 0; j < fieldName.size(); j++) {
			HSSFCell cell = headRow.createCell(j);
			if(fieldName.get(j) != null){
				cell.setCellValue((String) fieldName.get(j));
			}else{
				cell.setCellValue(" ");
			}
		}
	}
	
	//创建表格里面的数据
	public void createExcelContextNoPage(HSSFSheet sheet){
		for (int i = 0; i < fieldData.size(); i++) {
			HSSFRow headRow = null;
			if(fieldName!=null && fieldName.size()>0) {
				headRow=sheet.createRow((short)i+1);
			}else {
				headRow=sheet.createRow((short)i);
			}
			ArrayList<String> arrayList = fieldData.get(i);
			for (int j = 0; j < arrayList.size(); j++) {
				HSSFCell cell = headRow.createCell(j);
				String str = arrayList.get(j);
				if(str!=null){
					cell.setCellValue(str);
				}else{
					cell.setCellValue("");
				}
			}
		}
	}
	
	//创建表格里面的数据
	public void createExcelContext(HSSFSheet sheet,int i,int rows){
		for (int k = 0; k < (rows < SPLIT_COUNT ? rows : SPLIT_COUNT); k++) {
			if (((i - 1) * SPLIT_COUNT + k) >= rows)break;
			HSSFRow row = sheet.createRow((short) (k + 1));
			ArrayList<String> rowList = (ArrayList<String>) fieldData.get((i - 1) * SPLIT_COUNT + k);
			for (int n = 0; n < rowList.size(); n++) {
				HSSFCell cell = row.createCell(n);
				if(rowList.get(n) != null){
					cell.setCellValue((String) rowList.get(n).toString());
				}else{
					cell.setCellValue("");
				}
			}
		}
	}
	
	/**  
	* 读取excel文件  
	* @param wb   
	* @param sheetIndex sheet页下标：从0开始  
	* @param startReadLine 开始读取的行:从0开始  
	* @param tailLine 去除最后读取的行  
	*/
	public static List<List<String>> readExcel(Workbook wb, int sheetIndex, int startReadLine, int tailLine) {
		List<List<String>> arrayList = new ArrayList<List<String>>();
		Sheet sheet = wb.getSheetAt(sheetIndex);
		Row row = null;
		String cellValue = null;
        //获取最多有多少表格
		short sumCall = wb.getSheetAt(0).getRow(0).getLastCellNum();
		int maxColumn = Integer.parseInt(sumCall + "");
		//循环读取每行数据
		for (int i = startReadLine; i < sheet.getLastRowNum() - tailLine + 1; i++) {
			//数据为空跳出循环
			row = sheet.getRow(i);
			if(row ==null){
				continue;
			}
			//一行数据是否为null的标示
			int cellFlag = 0;
			List<String> lsit = new ArrayList<String>();
			//获取每行数据最大下标值
			int lastColumn = Math.max(row.getLastCellNum(), 0);
			//获取每行数据最小下标值
			int firstColumn = Math.max(row.getFirstCellNum(), 0);
			//初始化没有定义的单元格(最前面的单元格)
			if (firstColumn > 0) {
				for (int j = firstColumn - 1; j > -1; j--) {
					lsit.add(null);
					cellFlag++;
				}
			}
			//遍历每行数据里面的每个单元格
			for (int cn = 0; cn < lastColumn; cn++) {
				//设置表格读取没有定义或为空的单元格
				Cell c = row.getCell(cn, Row.RETURN_BLANK_AS_NULL);
				boolean isMerge = isMergedRegion(sheet, i, cn);
				// 判断是否具有合并单元格
				if (isMerge) {
					cellValue = getMergedRegionValue(sheet, startReadLine, cn);
				} else {
					cellValue = getCellValue(c);
				}
				if (cellValue != null && cellValue.trim().length() > 0 && !"null".equals(cellValue)) {
					lsit.add(cellValue);
				} else {
					lsit.add(null);
					//数据为空+1
					cellFlag++;
				}
			}
			//初始化没有定义的表格(最后面的单元格)
			if (maxColumn > lastColumn) {
				for (int j = lastColumn; j < maxColumn; j++) {
					lsit.add(null);
					cellFlag++;
				}
			}
			//判断一行数据是否都为空，不为空加入到list集合
			if (maxColumn != 0 && maxColumn > cellFlag) {
				arrayList.add(lsit);
			}
		}
		return arrayList;
	}

	/**   
	* 获取合并单元格的值   
	* @param sheet   
	* @param row   
	* @param column   
	* @return   
	*/
	public static String getMergedRegionValue(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress ca = sheet.getMergedRegion(i);
			int firstColumn = ca.getFirstColumn();
			int lastColumn = ca.getLastColumn();
			int firstRow = ca.getFirstRow();
			int lastRow = ca.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					Row fRow = sheet.getRow(firstRow);
					Cell fCell = fRow.getCell(firstColumn);
					return getCellValue(fCell);
				}
			}
		}
		return null;
	}

	/**  
	* 判断合并了行  
	* @param sheet  
	* @param row  
	* @param column  
	* @return  
	*/
	public static boolean isMergedRow(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			if (row == firstRow && row == lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					return true;
				}
			}
		}
		return false;
	}

	/**  
	* 判断指定的单元格是否是合并单元格  
	* @param sheet   
	* @param row 行下标  
	* @param column 列下标  
	* @return  
	*/
	public static boolean isMergedRegion(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					return true;
				}
			}
		}
		return false;
	}

	/**  
	* 判断sheet页中是否含有合并单元格   
	* @param sheet   
	* @return  
	*/
	public static boolean hasMerged(Sheet sheet) {
		return sheet.getNumMergedRegions() > 0 ? true : false;
	}

	/**  
	* 合并单元格  
	* @param sheet   
	* @param firstRow 开始行  
	* @param lastRow 结束行  
	* @param firstCol 开始列  
	* @param lastCol 结束列  
	*/
	public static void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
		sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
	}

	/**   
	* 获取单元格的值   
	* @param cell   
	* @return   
	*/
	public static String getCellValue(Cell cell) {
		if (cell == null) {
			return null;
		}
		if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
			return cell.getStringCellValue();
		} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
			return cell.getCellFormula();
		} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			if(HSSFDateUtil.isCellDateFormatted(cell)){//转换为日期格式
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  
				Date date = cell.getDateCellValue();
				return sdf.format(date);
			}
			return String.valueOf(cell.getNumericCellValue());
			
		}
		return null;
	}
}